﻿using Aspose.Cells;
using ProjectCloth.Entity;
using ProjectCloth.FrmUI.Common;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProjectCloth.FrmUI
{
    public partial class ImportProduct : Form
    {
        public ImportProduct()
        {
            InitializeComponent();

        }

        private void exportBtn_Click(object sender, EventArgs e)
        {
            //label1.Text = "";
            //FileDialog fileDialog = new FileDialog();
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = true;//该值确定是否可以选择多个文件
            dialog.Title = "请选择文件夹";
            dialog.Filter = "所有文件(*.*)|*.*";
            string file = "";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                file = dialog.FileName;
                var data = ExcelToTable(file);
                label1.Text =  data.ToString();

            }
            MessageBox.Show("窗口将关闭！");
            this.Close();
        }
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select no,name,batchprice,price from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="admin"></param>
        /// <returns></returns>
        public string ExcelToTable(string filePath)
        {
            Workbook book = null;
            Worksheet sheet = null;
            book = new Workbook(filePath);
            sheet = book.Worksheets[0];
            var result = 0;
            int i = 1;
            int s = 1;
            int count = 0;
            List<string> stockAccountList = new List<string>();
            StringBuilder sb = new StringBuilder();
            List<Product> list = new List<Product>();

            try
            {

                while (true)
                {
                    Cell celll = sheet.Cells[s, 1];
                    if (celll != null && !string.IsNullOrEmpty(celll.StringValue))
                    {
                        //1
                        string name = celll.StringValue;
                        //2
                        Cell cell2 = sheet.Cells[s, 2] != null ? sheet.Cells[s, 2] : null;
                        decimal batchprice = 0m;
                        if (cell2 != null && !string.IsNullOrEmpty(cell2.StringValue))
                            batchprice = Convert.ToDecimal(cell2.StringValue);
                        //3
                        Cell cell3 = sheet.Cells[s, 3] != null ? sheet.Cells[s, 3] : null;
                        decimal price = 0m;
                        if (cell3 != null && !string.IsNullOrEmpty(cell3.StringValue))
                            price = Convert.ToDecimal(cell3.StringValue);
                        var product = new Product()
                        {
                            ProductNo = name,
                            Price = price,
                            BatchPrice = batchprice,

                            ProductLevel = GetStringFromCell(sheet.Cells[s, 5]) == "" ? GetStringFromCell(sheet.Cells[s, 5]) : "N",
                            ProductNoB = GetStringFromCell(sheet.Cells[s, 6]),
                            ProductCost = GetStringFromCell(sheet.Cells[s, 7]),
                            Producer = GetStringFromCell(sheet.Cells[s, 8]),
                            Creator = GetStringFromCell(sheet.Cells[s, 9]),
                            Composition = GetStringFromCell(sheet.Cells[s, 10]),
                            ProductType = GetStringFromCell(sheet.Cells[s, 11]),
                            ProductWidth = GetStringFromCell(sheet.Cells[s, 12]),
                            ExtRemark = GetStringFromCell(sheet.Cells[s, 13]),
                            CreateTime = DateTime.Now,
                        };
                        list.Add(product);
                        s++;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            catch (Exception error)
            {
                Helper.WriteLog(error.Message);
            }
            finally
            {

            }
            var NewProductList = new List<Product>();
            ClothContext db = new ClothContext();
            var updateCount = 0;
            foreach (var product in list)
            {
                var dbproduct = db.Products.Where(o => o.ProductNo == product.ProductNo).FirstOrDefault();
                if (dbproduct == null || string.IsNullOrEmpty(dbproduct.ProductNo))
                {
                    NewProductList.Add(product);
                }
                else
                {
                    dbproduct.Price = product.Price;
                    dbproduct.BatchPrice = product.BatchPrice;
                    dbproduct.ProductLevel = product.ProductLevel;
                    dbproduct.ProductNoB = product.ProductNoB;
                    dbproduct.ProductCost = product.ProductCost;
                    dbproduct.Producer = product.Producer;
                    dbproduct.Creator = product.Creator;
                    dbproduct.Composition = product.Composition;
                    dbproduct.ProductType = product.ProductType;
                    dbproduct.ProductWidth = product.ProductWidth;
                    dbproduct.ExtRemark = product.ExtRemark;
                    updateCount+= db.SaveChanges();
                }

            }
            db.Products.AddRange(NewProductList);
            result = db.SaveChanges();
            return "新增条目："+result+"，更新条目："+updateCount.ToString();
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }
        /// <summary>
        /// 转换成字符串
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private Decimal GetDecimalFromCell(Cell cell)
        {
            try
            {
                if (cell != null && !string.IsNullOrEmpty(cell.StringValue))
                {
                    return Convert.ToDecimal("0" + cell.StringValue.Trim());
                }
            }
            catch (Exception error)
            {
                Helper.WriteLog(error.Message);
            }
            return 0;
        }
        /// <summary>
        /// 转换成decimal
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private string GetStringFromCell(Cell cell)
        {
            if (cell != null && !string.IsNullOrEmpty(cell.StringValue))
            {
                return cell.StringValue.Trim();
            }
            return "";
        }
    }
}